var queues = require('mysql-queues');
const DEBUG = true;
var selAll = function(connection, callback) {
	var sql = "select * from item where type not in ('权限') order by type";
	connection.query(sql, function(err, rows) {
		if (err){
			console.log(err);
			console.log(err.stack);
		}
		callback(rows);
		connection.release();
	});
},

    //根据类型的关键字查询选项
    selByKeyWord = function(connection, keyword, callback) {
    	var sql = "select t.* from (select * from item where type like ? or showValue like ?)t where t.type not in ('权限') order by t.type";
    	connection.query(sql,['%'+keyword+'%', '%'+keyword+'%'], function(err, rows) {
    		if(err){
    			console.log(err);
    			console.log(err.stack);
    			callback(null);
    		}else{
    			callback(rows);
    		}
    	})
    },

	insertItem = function(connection, data, callback) {
		queues(connection, DEBUG);
		// console.log("insertItem--data:", data);
		var sql = 'insert into item(type,showValue,value) values(?,?,?)';
		var trans = connection.startTransaction(); 
		trans.query(sql, [data.type, data.showValue, data.value], function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					console.log(info);
				});
			}
			callback(rows);
		});
		trans.execute();
		console.log('execute insertItem');
		connection.release();
	},
	selByShow = function(connection, data, callback) {
		var sql = "select t.* from (select * from item where showValue = ? and type = ?)t where t.type not in ('权限')";
		connection.query(sql,[data.showValue, data.type], function(err, rows) {
			if(err) {
				callback(null);
			}else{
				callback(rows);
			}
			connection.release();
		});
	},

	selByValue = function(connection, value, callback) {
		var sql = "select * from item where value = ? and type not in ('权限')";
		connection.query(sql, value, function(err, rows) {
			if(err)
				callback(null);
			else
				callback(rows);
			connection.release();
		});
	},

	delItem = function(connection, id, callback) {
		queues(connection, DEBUG);
		var sql = 'delete from item where id=?';
		var trans = connection.startTransaction();
		trans.query(sql, id, function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					console.log(info);
				});
			}
			console.log('delItem--rows', rows);
			callback(rows);
		});
		trans.execute();
		console.log('execute');
		connection.release();
	},
	updateItem = function(connection, data, callback) {
		queues(connection, DEBUG);
		var sql = 'update item set type=?, showValue=?, value=? where id=?';
		var trans = connection.startTransaction();
		trans.query(sql, [data.type,data.showValue,data.value, data.id], function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					console.log('info:', info);
				});
			}
			console.log('rows', rows);
			callback(rows);
		});
		trans.execute();
		console.log('execute update');
		connection.release();
	},
	findById = function(connection, id, callback) {
		var sql = "select * from item where id=? and type not in ('权限')";
		connection.query(sql, id, function(err, rows) {
			callback(rows);
			connection.release();
		});
	},
	//更新开放的权限
	updateAccess = function(connection, data, callback) {
		queues(connection, DEBUG);
		var sql = "delete from item where type = '权限'";
		var sql2 = "insert into item(type,showValue,value) values(?,?,?)";
		var trans = connection.startTransaction();
		trans.query(sql, function(err, result1) {
			if(err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			}else{
				for(var i = 0; i < data.length; i++) {
					trans.query(sql2, [data[i].type, data[i].showValue, data[i].value], function(err, result2) {
						if(err) {
							console.log(err);
							console.log(err.stack);
							trans.rollback();
						}
					});
				}
				trans.commit();
			}
		});
		trans.execute();
		connection.release();
		callback("success");
	},
	//查询开放的权限
	selAccess = function(connection,showValue, callback) {
		var sql = "";
		if(showValue == null || showValue == undefined) {
			sql = "select * from item where type = '权限'";
		}else{
			sql = "select * from item where type = '权限' and showValue = '" + showValue + "'";
		}
		// var sql = "select * from item where type = '权限'";
		connection.query(sql, function(err, rows) {
			if(err) {
				console.log(err);
				console.log(err.stack);
				callback(null);
			}else{
				callback(rows);
			}
		});
		connection.release();
	},

	//根据类型查询满足关键字的选项
    selByKey = function(connection, keyword, callback) {
    	var sql = "select t.* from (select * from item where type=?)t where t.type not in ('权限')order by t.type";
    	connection.query(sql,keyword, function(err, rows) {
    		if(err){
    			console.log(err);
    			console.log(err.stack);
    			callback(null);
    		}else{
    			callback(rows);
    		}
    	})
    };

module.exports = {
	selAll: selAll,
	insertItem: insertItem,
	delItem: delItem,
	updateItem: updateItem,
	findById: findById,
	selByShow: selByShow,
	selByValue: selByValue,
	selByKeyWord: selByKeyWord,
	selByKey: selByKey,
	updateAccess: updateAccess,
	selAccess: selAccess
};